﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace ComputerVision.Update
{
    public class BaseDAO
    {

        public static SqlConnection Connect()
        {
            SqlConnection connection = null;
            string connectString = "data source=192.168.1.100;User ID=sa;password=123456;initial catalog=ComputerVision;";

            //string connectString = "server=.;uid=sa; pwd=123456; database=rrc_database;";	

            connection = new SqlConnection(connectString);
            try
            {
                connection.Open();
                return connection;
            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;

            }

        }

        public static DataTable ExcuteProcedure(string storeProcedure)
        {
            SqlCommand command = new SqlCommand();
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataTable dataTable = new DataTable();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = 0;
            try
            {
                command.CommandText = storeProcedure;
                command.Parameters.Clear();
                command.Connection = Connect();
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
                Connect().Close();

                return dataTable;
            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }


        }

        public static DataSet ExcuteProcedureDS(string storeProcedure)
        {
            SqlCommand command = new SqlCommand();
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = 0;
            try
            {
                command.CommandText = storeProcedure;
                command.Parameters.Clear();
                command.Connection = Connect();

                adapter.SelectCommand = command;
                adapter.Fill(ds);
                Connect().Close();

                return ds;
            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }


        }

        public static DataTable ExcuteProcedure(string storeProcedure, SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand();
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataTable dataTable = new DataTable();

            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = storeProcedure;
            command.CommandTimeout = 0;
            try
            {
                foreach (SqlParameter sqlParameter in parameters)
                {
                    command.Parameters.Add(sqlParameter);
                }

                command.Connection = Connect();
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
                Connect().Close();

                return dataTable;

            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;

            }

        }

        public static int ExecuteNonQueryProcedure(string storeProcedure, SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = storeProcedure;
            command.CommandTimeout = 0;
            int result = 0;
            try
            {
                foreach (SqlParameter sqlParameter in parameters)
                {
                    command.Parameters.Add(sqlParameter);
                }

                command.Connection = Connect();
                result = command.ExecuteNonQuery();
                Connect().Close();

                return result;

            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return result;
            }
        }

        public static int ExecuteNonQuery(string inputQuery)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = inputQuery;
            command.CommandTimeout = 0;
            int result = 0;
            try
            {
                command.Connection = Connect();
                result = command.ExecuteNonQuery();
                Connect().Close();

                return result;

            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return result;
            }
        }

        public static object ExecuteScalar(string inputQuery)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = inputQuery;
            command.CommandTimeout = 0;
            object result = null;
            try
            {
                command.Connection = Connect();
                result = command.ExecuteScalar();
                Connect().Close();

                return result;

            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return result;
            }
        }

        public static DataTable ExecuteQuery(string inputQuery)
        {
            SqlCommand command = new SqlCommand();
            SqlDataAdapter adapter;
            command.CommandType = CommandType.Text;
            command.CommandText = inputQuery;
            command.CommandTimeout = 0;
            DataTable result = new DataTable();
            try
            {
                command.Connection = Connect();
                adapter = new SqlDataAdapter(command);
                adapter.Fill(result);
                Connect().Close();

                return result;

            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return result;
            }
        }

        public static SqlDataReader DataReaderSp(string storeProcedure)
        {

            try
            {
                SqlCommand command = Connect().CreateCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = storeProcedure;
                command.CommandTimeout = 0;
                SqlDataReader result = command.ExecuteReader();

                return result;
            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
        }

        public static SqlParameter CreateParameter(string parameterName, SqlDbType type, object value)
        {
            SqlParameter parameter = new SqlParameter();

            try
            {
                parameter.ParameterName = parameterName;
                parameter.SqlDbType = type;
                parameter.Value = value;

                return parameter;
            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }

        }


    }

}
